package com.hll.dao.user;

import com.hll.dao.BaseDao;
import com.hll.pojo.User;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: Mr.Hu
 * @create: 2021-03-07 17:15
 */
public class UserDaoImpl implements UserDao {

  @Override
  public User getLoginUser(Connection connection, String userCode, String pwd) throws SQLException {
    PreparedStatement statement = null;
    ResultSet rs;
    User user = null;

    if (null != connection) {
      String sql = "select * from smbms_user where userCode = ? and userPassword = ?";
      Object[] params = {userCode, pwd}; // 可变长参数可传数组
      rs = BaseDao.execute(connection, sql, statement, params); // 通过基类
      if (rs.next()) {
        user = new User();
        user.setId(rs.getInt("id"));
        user.setUserCode(rs.getString("userCode"));
        user.setUserName(rs.getString("userName"));
        user.setUserPassword(rs.getString("userPassword"));
        user.setGender(rs.getInt("gender"));
        user.setBirthday(rs.getDate("birthday"));
        user.setPhone(rs.getString("phone"));
        user.setAddress(rs.getString("address"));
        user.setUserRole(rs.getInt("userRole"));
        user.setCreatedBy(rs.getInt("createdBy"));
        user.setCreationDate(rs.getTimestamp("creationDate"));
        user.setModifyBy(rs.getInt("modifyBy"));
        user.setModifyDate(rs.getTimestamp("modifyDate"));
      }
      BaseDao.release(null, statement, rs); // 别忘了,connection service层关闭
    }
    return user;
  }

  @Override
  public int updatePwd(Connection connection, int id, String newPwd) throws SQLException {
    PreparedStatement statement = null;
    int row = 0;
    if (null != connection) {
      String sql = "update smbms_user set userPassword = ? where id = ?";
      Object[] params = {newPwd, id}; // 写成了 id,pwd 调了好久....
      row = BaseDao.executeUpdate(connection, sql, statement, params);
      BaseDao.release(null, statement, null);
    }
    return row;
  }

  @Override
  public int getUserCount(Connection connection, String username, Integer role)
      throws SQLException {
    PreparedStatement statement = null;
    ResultSet rs = null;
    int count = 0;
    if (connection != null) {
      StringBuffer sql = new StringBuffer();
      /*查询所有用户, 这时联合两张表搜索,只有user.userRole 在 role.id中存在才检索, 正好省去了我考虑where的问题*/
      sql.append(
          "select count(*) as count from smbms_user as u,smbms_role as r where u.userRole=r.id");
      // 逐个增加检索条件
      ArrayList<Object> params = new ArrayList<>();
      if (!StringUtils.isNullOrEmpty(username)) {
        sql.append(" and userName like ?"); // 注意前加空格
        params.add("%" + username + "%"); // 假如搜索like '%李%',那么任意位置有李的都会检索
      }

      if (role != null && role > 0) {
        sql.append(" and userRole = ?");
        params.add(role);
      }
      rs =
          BaseDao.execute(
              connection, sql.toString(), statement, params.toArray()); // 注意要养params转为数组

      if (rs.next()) {
        count = rs.getInt(1);
      }
      BaseDao.release(null, statement, rs);
    }
    return count;
  }

  @Override
  public List<User> getUserList(
      Connection connection, String userName, Integer role, int currentPageNo, int pageSize)
      throws SQLException {
    PreparedStatement statement = null;
    ResultSet rs = null;
    List<User> userList = null; // 用户表
    if (connection != null) {
      StringBuffer sql = new StringBuffer();
      // u.* ,r.roleName
      sql.append(
          "select u.*, r.roleName from smbms_user as u,smbms_role as r where u.userRole = r.id");

      ArrayList<Object> params = new ArrayList<>();
      if (!StringUtils.isNullOrEmpty(userName)) {
        sql.append(" and userName like ?"); // 注意前加空格
        params.add("%" + userName + "%"); // 假如搜索like '%李%',那么任意位置有李的都会检索
      }

      if (role != null && role > 0) {
        sql.append(" and userRole = ?");
        params.add(role);
      }

      // 分页
      sql.append(" order by creationDate DESC limit ?,?");
      currentPageNo = (currentPageNo - 1) * pageSize;
      params.add(currentPageNo);
      params.add(pageSize);
      rs =
          BaseDao.execute(
              connection, sql.toString(), statement, params.toArray()); // 注意要养params转为数组

      // 添加
      userList = new ArrayList<>();
      while (rs.next()) {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setUserCode(rs.getString("userCode"));
        user.setUserName(rs.getString("userName"));
        user.setUserPassword(rs.getString("userPassword"));
        user.setGender(rs.getInt("gender"));
        user.setBirthday(rs.getDate("birthday"));
        user.setPhone(rs.getString("phone"));
        user.setAddress(rs.getString("address"));
        user.setUserRole(rs.getInt("userRole"));
        user.setCreatedBy(rs.getInt("createdBy"));
        user.setCreationDate(rs.getTimestamp("creationDate"));
        user.setModifyBy(rs.getInt("modifyBy"));
        user.setModifyDate(rs.getTimestamp("modifyDate"));
        user.setUserRoleName(rs.getString("roleName"));
        userList.add(user);
      }

      BaseDao.release(null, statement, rs);
    }
    return userList;
  }
}
